﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Using_List')
	BEGIN
		DROP Procedure usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Using_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


/****************************************************************************** 
**	Name : usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Using_List   
**	Desc : 월간차량비용내역 - 주유,정비,주차,통행료를 달력형태로 출력
**	Test Exec Query : Exec usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Using_List 'CAR01', '2010-06', 'ko'
**	Called by : Car_Dac_UPDMS_CAR_REPORT_Car2013r.cs
**	Program ID : Car2013r
**	Auth : 송시명
**	Date : 2010-07-12
******************************************************************************* 
**	Change History 
******************************************************************************* 
**	Date:		Author:		Description: 
**	--------	--------	--------------------------------------- 
**	
*******************************************************************************/ 
CREATE PROC [dbo].[usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Using_List]
@ls_car_id nvarchar(5),
@ls_base_ym nvarchar(7),
@ls_lang_set nvarchar(2)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT ISNULL([일], '') AS Sun,
       ISNULL([월], '') AS Mon,
       ISNULL([화], '') AS Tue,
       ISNULL([수], '') AS Wed,
       ISNULL([목], '') AS Thu,
       ISNULL([금], '') AS Fri,
       ISNULL([토], '') AS Sat
  FROM
     (
       SELECT z.Day_K_Abb_Nm,
              umd.Week_K_Nm,
              '<span class="dt' + CASE umd.Holiday_Yn WHEN 1 THEN ' color_red' ELSE '' END +
              CASE LEFT(CONVERT(VARCHAR, GETDATE(), 121), 10) WHEN umd.Dt THEN ' today' ELSE '' 
               END + '">' + RIGHT(umd.Dt, 2) + '</span><br>' + z.Contents AS Contents
         FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
         LEFT OUTER JOIN
            (
              SELECT DISTINCT Day_K_Abb_Nm,
                     Dt,
                     REPLACE(REPLACE(ISNULL(Contents,''), '&lt;', '<'),'&gt;', '>') AS Contents
                FROM
                   (
                     SELECT Day_K_Abb_Nm,
                            umd.Dt,
                            ( SELECT LTRIM(Contents) + '<br>' AS [text()]
                                FROM
                                   (
                                     --주유
                                     SELECT Refuelling_Dt AS Biz_Dt,
                                            '<span class="sicon_refuelling"></span><span title="' + Remark + '">'
                                            + ucs.Station_Nm + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>' AS Contents
                                       FROM UPDMS_CAR_REFUELLING AS ucr WITH(NOLOCK)
                                       JOIN UPDMS_CAR_STATION AS ucs WITH(NOLOCK) ON ucr.Station_Id = ucs.Station_Id
                                      WHERE Car_Id = @ls_car_id
                                        AND LEFT(Refuelling_Dt, 7) = @ls_base_ym
                                     UNION ALL
                                     --정비
                                     SELECT Service_Dt,
                                            '<span class="sicon_service"></span><span title="' + Remark + '">'
                                            + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
                                       FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
                                      WHERE Car_Id = @ls_car_id
									    AND Div <> 'AD003'
                                        AND LEFT(Service_Dt, 7) = @ls_base_ym
                                     UNION ALL
                                     --주차,통행료(카드)
                                     SELECT Biz_Dt,
                                            '<span class="sicon_traffic"></span><span title="' + Remark + '">'
                                            + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
                                       FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
                                      WHERE Account = 'AC024' 
                                        AND LEFT(Biz_Dt, 7) = @ls_base_ym
                                     UNION ALL
                                     --주차,통행료(현금)
                                     SELECT Reg_Dt,
                                            '<span class="sicon_traffic"></span><span title="' + Remark + '">'
                                            + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
                                       FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
                                      WHERE Account = 'AC024' 
                                        AND LEFT(Reg_Dt, 7) = @ls_base_ym
                                     UNION ALL
                                     --세차
                                     SELECT Service_Dt,
                                            '<span class="sicon_washing"></span><span title="' + Remark + '">'
                                            + Contents + '[' + dbo.ufn_UPDMS_Convert_Money(Amount)+']</span>'
                                       FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
                                      WHERE Car_Id = @ls_car_id
									    AND Div = 'AD003'
                                        AND LEFT(Service_Dt, 7) = @ls_base_ym
                                   ) tmp
                               WHERE tmp.Biz_Dt = ucrs.Biz_Dt FOR XML PATH('')
                             ) AS Contents
                        FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
                        LEFT OUTER JOIN
                           (
							 SELECT Refuelling_Dt AS Biz_Dt
							   FROM UPDMS_CAR_REFUELLING WITH(NOLOCK)
							  WHERE Car_Id = @ls_car_id
                                AND LEFT(Refuelling_Dt, 7) = @ls_base_ym
							 UNION ALL
							 SELECT Service_Dt
							   FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
							  WHERE Car_Id = @ls_car_id
                                AND LEFT(Service_Dt, 7) = @ls_base_ym
                             UNION ALL
							 SELECT Biz_Dt
							   FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
							  WHERE Account = 'AC024' 
                                AND LEFT(Biz_Dt, 7) = @ls_base_ym
                             UNION ALL
							 SELECT Reg_Dt
							   FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
							  WHERE Account = 'AC024' 
                                AND LEFT(Reg_Dt, 7) = @ls_base_ym
                           ) AS ucrs
                          ON umd.Dt = ucrs.Biz_Dt
                       WHERE umd.Yyyy_Mm = @ls_base_ym
                    ) AS rslt
            ) AS z
           ON umd.Dt = z.Dt
        WHERE umd.Yyyy_Mm = @ls_base_ym
     ) AS pv
 PIVOT ( MAX(pv.Contents) FOR pv.Day_K_Abb_Nm IN ([일],[월],[화],[수],[목],[금],[토])
       ) AS pvt

GO
